#!/bin/bash

# 地图区域表结构修改脚本
# 执行前请确保已备份数据库

set -e

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 数据库配置
DB_HOST="43.143.253.188"
DB_PORT="3306"
DB_USER="nextjs"
DB_PASS="yEAiRE2jcxTyRBy5"
DB_NAME="nextjs"

# 脚本路径
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
MIGRATION_FILE="$SCRIPT_DIR/../database/alter-map-areas-table.sql"

echo -e "${BLUE}🚀 开始执行地图区域表结构修改...${NC}"
echo ""

# 检查迁移文件是否存在
if [ ! -f "$MIGRATION_FILE" ]; then
    echo -e "${RED}❌ 迁移文件不存在: $MIGRATION_FILE${NC}"
    exit 1
fi

echo -e "${GREEN}✅ 找到迁移文件: $MIGRATION_FILE${NC}"

# 显示迁移文件内容预览
echo ""
echo -e "${YELLOW}📋 迁移文件内容预览:${NC}"
echo "----------------------------------------"
head -20 "$MIGRATION_FILE"
echo "..."
echo "----------------------------------------"
echo ""

# 确认执行
read -p "⚠️  确认要执行此迁移吗？这将修改数据库表结构 (y/N): " -n 1 -r
echo
if [[ ! $REPLY =~ ^[Yy]$ ]]; then
    echo -e "${YELLOW}❌ 用户取消执行${NC}"
    exit 0
fi

echo ""
echo -e "${BLUE}🔗 连接数据库...${NC}"

# 测试数据库连接
if ! mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "SELECT 1;" > /dev/null 2>&1; then
    echo -e "${RED}❌ 数据库连接失败${NC}"
    echo "请检查以下配置："
    echo "  - 主机: $DB_HOST"
    echo "  - 端口: $DB_PORT"
    echo "  - 用户: $DB_USER"
    echo "  - 数据库: $DB_NAME"
    exit 1
fi

echo -e "${GREEN}✅ 数据库连接成功${NC}"

# 备份当前表结构
echo ""
echo -e "${BLUE}💾 备份当前表结构...${NC}"
BACKUP_FILE="$SCRIPT_DIR/../database/map_areas_backup_$(date +%Y%m%d_%H%M%S).sql"

mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "
SELECT CONCAT('DROP TABLE IF EXISTS \`', TABLE_NAME, '\`;') as 'DROP_STATEMENT'
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '$DB_NAME' AND TABLE_NAME = 'map_areas';
" > "$BACKUP_FILE"

mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "
SHOW CREATE TABLE map_areas;
" >> "$BACKUP_FILE"

echo -e "${GREEN}✅ 表结构已备份到: $BACKUP_FILE${NC}"

# 执行迁移
echo ""
echo -e "${BLUE}🔧 执行表结构修改...${NC}"

if mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$MIGRATION_FILE"; then
    echo -e "${GREEN}✅ 表结构修改成功${NC}"
else
    echo -e "${RED}❌ 表结构修改失败${NC}"
    echo "请检查错误信息并手动修复"
    exit 1
fi

# 验证修改结果
echo ""
echo -e "${BLUE}🔍 验证修改结果...${NC}"

mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "
SELECT 
    COUNT(*) as '总区域数',
    COUNT(parent_id) as '有父级区域数',
    COUNT(CASE WHEN parent_id IS NULL THEN 1 END) as '顶级区域数',
    COUNT(extension) as '有等级信息区域数'
FROM map_areas;
"

echo ""
echo -e "${BLUE}📊 区域层级关系示例:${NC}"

mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "
SELECT 
    \`id\`, 
    \`name\`, 
    \`parent_id\`, 
    \`extension\`,
    CASE 
        WHEN \`parent_id\` IS NULL THEN '顶级区域'
        ELSE CONCAT('子区域 (父级ID: ', \`parent_id\`, ')')
    END as \`区域类型\`
FROM \`map_areas\` 
ORDER BY \`parent_id\` NULLS FIRST, \`id\`
LIMIT 20;
"

echo ""
echo -e "${GREEN}🎉 地图区域表结构修改完成！${NC}"
echo ""
echo -e "${BLUE}📝 后续步骤:${NC}"
echo "1. 重启NestJS应用服务"
echo "2. 测试WebSocket地图查询功能"
echo "3. 运行测试脚本: node test-map-areas.js"
echo ""
echo -e "${YELLOW}⚠️  注意事项:${NC}"
echo "- 备份文件保存在: $BACKUP_FILE"
echo "- 如有问题可回滚到备份状态"
echo "- 建议在测试环境验证后再应用到生产环境" 